<?php

require("connect.inc.php");
require("readLog.inc.php");
require_once('external_channel_lib.php');

$NO_UPDATE = "NO_UPDATE";

//this function for searching the title.

//it implements into 2 cases

// 1. if user want to search in any channel (sending $channelid=0 to function)

// other wise please send the correct channelid for correct result

function searchContent($username, $keyword, $channelid, $page = 1, $num = 10) {

	global $db;

	$start = ($page - 1) * $num;

	// if channelid == 0, search in all channels that display on the screen

	// else search in only one channel that display on the screen

	if($channelid != 0) {

		/* Old sql query. the new one is executed faster.
		 $sql = "SELECT * FROM (
		 SELECT * FROM userchannel INNER JOIN channel
		 WHERE userchannel.channelid = channel.id
		 ) AS temp INNER JOIN item ON temp.channelid = item.channelid
		 WHERE temp.channelid = '$channelid'
		 AND item.isPublish = 1
		 AND (LOWER(item.title) LIKE LOWER('%". $keyword . "%')
		 OR LOWER(item.description) LIKE LOWER('%". $keyword . "%'))
		 AND temp.username = '$username'
		 ORDER BY item.pubdate DESC";
		 */

		$sql = "SELECT * FROM userchannel
				INNER JOIN channel ON userchannel.channelid = channel.id 
				INNER JOIN item ON userchannel.channelid = item.channelid 
				WHERE userchannel.channelid = '" . $channelid . "' 
				AND item.isPublish = '1' 
				AND item.isRead = '1'
				AND userchannel.username = '" . $username . "' 
				AND ( 
					LOWER( item.title ) LIKE LOWER( '%". $keyword . "%' ) 
					OR LOWER( item.description ) LIKE LOWER( '%". $keyword . "%' )
				) ORDER BY item.pubdate DESC";

	} else {

		$sql = "SELECT * FROM userchannel
				INNER JOIN channel ON userchannel.channelid = channel.id 
				INNER JOIN item ON userchannel.channelid = item.channelid 
				WHERE item.isPublish = '1' 
				AND item.isRead = '1' 
				AND userchannel.username = '" . $username . "' 
				AND (
					LOWER( item.title ) LIKE LOWER( '%". $keyword . "%' ) 
					OR LOWER( item.description ) LIKE LOWER( '%". $keyword . "%' )
				) ORDER BY item.pubdate DESC";

	}

	$sql .= " LIMIT " . $start . ", " . $num;

	$result = $db->sql_query($sql);

	addReadLog($channelid, $username);

	return $db->sql_fetchrowset($result);

}

function searchUpdateContent($username, $keyword, $channelid, $page = 1, $num = 10) {

	global $db;

	//$start = ($page - 1) * $num;

	if($channelid != 0)
	{

		$sql = "SELECT channel.title AS channel_title, channel.rank, item.id, item.channelid, 
				item.title, item.link, item.description, item.real_pubDate, item.pubdate, 
				item.author, item.isPublish, item.isRead FROM userchannel
				INNER JOIN channel ON userchannel.channelid = channel.id 
				INNER JOIN item ON userchannel.channelid = item.channelid 
				WHERE userchannel.channelid = '" . $channelid . "' 
				AND item.isPublish = '1' 
				AND item.isRead = '0' 
				AND userchannel.username = '" . $username . "' 
				AND ( 
					LOWER( item.title ) LIKE LOWER( '%". $keyword . "%' ) 
					OR LOWER( item.description ) LIKE LOWER( '%". $keyword . "%' )
				) ORDER BY item.pubdate DESC";

	}
	else
	{

		$sql = "SELECT channel.title AS channel_title, channel.rank, item.id, item.channelid, 
				item.title, item.link, item.description, item.real_pubDate, item.pubdate, 
				item.author, item.isPublish, item.isRead FROM userchannel
				INNER JOIN channel ON userchannel.channelid = channel.id 
				INNER JOIN item ON userchannel.channelid = item.channelid 
				WHERE item.isPublish = '1' 
				AND item.isRead = '0' 
				AND userchannel.username = '" . $username . "' 
				AND (
					LOWER( item.title ) LIKE LOWER( '%". $keyword . "%' ) 
					OR LOWER( item.description ) LIKE LOWER( '%". $keyword . "%' )
				) ORDER BY item.pubdate DESC";

	}

	//$sql .= " LIMIT " . $start . ", " . $num;

	$result = $db->sql_query($sql);

	addReadLog($channelid, $username);

	return $db->sql_fetchrowset($result);

}

function getNumSearchContent($username, $keyword, $channelid) {

	global $db;

	if($channelid != 0) {
		$sql = "SELECT * FROM userchannel
				INNER JOIN channel ON userchannel.channelid = channel.id 
				INNER JOIN item ON userchannel.channelid = item.channelid 
				WHERE userchannel.channelid = '" . $channelid . "' 
				AND item.isPublish = '1' 
				AND userchannel.username = '" . $username . "' 
				AND ( 
					LOWER( item.title ) LIKE LOWER( '%". $keyword . "%' ) 
					OR LOWER( item.description ) LIKE LOWER( '%". $keyword . "%' )
				) ORDER BY item.pubdate DESC";
	} else {
		$sql = "SELECT * FROM userchannel
				INNER JOIN channel ON userchannel.channelid = channel.id 
				INNER JOIN item ON userchannel.channelid = item.channelid 
				WHERE item.isPublish = '1' 
				AND userchannel.username = '" . $username . "' 
				AND (
					LOWER( item.title ) LIKE LOWER( '%". $keyword . "%' ) 
					OR LOWER( item.description ) LIKE LOWER( '%". $keyword . "%' )
				) ORDER BY item.pubdate DESC";
	}

	$query = $db->sql_query($sql);
	return $db->sql_numrows($query);

}

// this function for Readers unsubscribe their channel.

function unSubscribe($username, $channelid){

	global $db;

	$sql = "LOCK TABLES userchannel WRITE, item WRITE";
	$db->sql_query($sql);

	// this query is Ok.

	$sql = "DELETE FROM userchannel WHERE username='$username' AND channelid='$channelid'";
	$db->sql_query($sql);


	$sql = "UPDATE item SET isRead = '0' WHERE channelid = '" . $channelid . "'";
	$db->sql_query($sql);

	$sql = "UNLOCK TABLES";
	$db->sql_query($sql);

}



// this is a function for reader to change their Channel's color to display

// input for processing : username, channelid and color.

function changeChannelColor($username, $channelid, $color){

	global $db;

	$boo = isColorValid($color);

	if($boo == 1){

		$sql = "LOCK TABLES userchannel WRITE";
		$db->sql_query($sql);

		// this query is Ok.

		$sql = "UPDATE userchannel SET color='$color' WHERE username='$username' AND channelid='$channelid'";
		$db->sql_query($sql);

		$sql = "UNLOCK TABLES";
		$db->sql_query($sql);

	}

	else { // Here, u can implement anything u want if the color is not valid

		echo "it's not a valid color";

	}

}



// this function is checking that an input of color from user is valid or not.

function isColorValid($color){

	$boo = 1;

	if(strlen($color)==6){

		$char = str_split($color);

		for($i=0;$i<6;$i++){

			if(isHex($char[$i])==0){

				$boo = 0;

				break;

			}

		}

	}

	else $boo=0;

	return $boo;

}



function isHex($char){

	if( ($char >= '0' && $char<='9') || ($char >= 'a' && $char<='f') || ($char >= 'A' && $char<='F'))

	return 1;

	else return 0;

}



// this function for Reader to set Setting Scheme

// bgcolor in format RRGGBB

// newsperpage in Integer type

// fullmode in Boolean type

function setReaderSetting($username, $bgcolor, $newsperpage, $fullmode) {

	global $db;

	$sql = "LOCK TABLES reader WRITE";
	$db->sql_query($sql);

	$sql = "UPDATE reader SET bgcolor = '$bgcolor', newsperpage = '$newsperpage', fullmode = $fullmode WHERE username = '$username'";
	$db->sql_query($sql);

	$sql = "UNLOCK TABLES";
	$db->sql_query($sql);

}



function getReaderSetting($username) {

	global $db;

	$sql = "SELECT bgcolor, newsperpage, fullmode FROM  reader WHERE username = '$username'";

	$query = $db->sql_query($sql);

	return $db->sql_fetchrow($query);

}



function getChannelSetting($username, $cid) {

	global $db;

	$sql = "SELECT color FROM  userchannel WHERE username = '$username' AND channelid = '$cid'";

	$query = $db->sql_query($sql);

	return $db->sql_fetchrow($query);

}



////////////Opal//////////////



function setItemRead($username, $itemid, $channelid) {

	$sql = "LOCK TABLES useritem WRITE";
	$db->sql_query($sql);

	$sql = "INSERT INTO useritem VALUES ('" . $username . "', '" . $itemid . "', '" . $channelid . "')";
	$db->sql_query($sql);

	$sql = "UNLOCK TABLES";
	$db->sql_query($sql);

}



function arrayToPage($array, $pageIndex, $itemsPerPage) {

	$ret = array();

	for($i = 0; $i < $itemsPerPage; $i++) {

		$item = $array[($pageIndex - 1) * $itemsPerPage + $i];

		if($item == null) {

			break;

		}

		$ret[$i] = $item;

	}

	return $ret;

}

function viewFeed($username){

	$query = 'SELECT * FROM item INNER JOIN (SELECT * FROM userchannel WHERE

			  username="'.$username.'") AS tempuserchannel

			  ON item.channelid = tempuserchannel.channelid AND item.isPublic=1

			  ORDER BY item.id DESC';

	$result = mysql_query($query) or die(mysql_error());

	while($r = mysql_fetch_assoc($result)) {
		$row[] = $r;
	}

	return $row;

}



function viewChannel($username,$rank) {

	$query = 'SELECT * FROM (SELECT channelid, count(*) AS unread FROM item WHERE isPublish=1 AND

	          id NOT IN (SELECT itemid FROM useritem WHERE username = "'.$username.'") 

			  GROUP BY channelid) AS first RIGHT JOIN

              (SELECT * FROM channel RIGHT JOIN 

              (SELECT * FROM userchannel WHERE

			  username="'.$username.'") AS tempuserchannel

			  ON channel.id = tempuserchannel.channelid

			  WHERE rank <= "'.$rank.'" ORDER BY channel.id ASC) AS second

			  ON first.channelid=second.id';

	$result = mysql_query($query) or die(mysql_error());

	while($r = mysql_fetch_assoc($result)) {
		$row[] = $r;
	}

	return $row;

}



function subscribe($username,$channel){

	$sql = "LOCK TABLES userchannel WRITE";
	mysql_query($sql);

	$query = sprintf('INSERT INTO %s (username, channelid, color) values ("%s", "%d", "%s")', "userchannel", $username, $channel, "FFFFFF");
	mysql_query($query) or die(mysql_error());

	$sql = "UNLOCK TABLES";
	mysql_query($sql);

}



function getAvailableChannel($username,$rank){
	$query = "SELECT * , externalfeed.link AS exLink
	FROM (

	SELECT *
	FROM channel
	WHERE id NOT
	IN (

	SELECT channelid AS id
	FROM userchannel
	WHERE username = '$username'
	)
	AND rank <= '$rank'
	) AS a
	LEFT JOIN externalfeed ON a.docs = externalfeed.link
	ORDER BY id ASC";
	//echo $query;
	$result = mysql_query($query) or die(mysql_error());
	while($r=mysql_fetch_assoc($result)){$row[]=$r;}
	return $row;
}



//additional

function getUserChannelBy($id,$username) {

	global $db;

	$query = "SELECT * FROM userchannel WHERE channelid = '$id' AND username = '$username'";

	$result = mysql_query($query) or die(mysql_error());

	while($r=mysql_fetch_assoc($result)){$row[]=$r;}

	return $row;

	$result = $db->sql_query($sql);

	return $db->sql_fetchrow($result);

}



function getUpdatedItemTime() {

	global $db;

	$sql = "SELECT update_time FROM information_schema WHERE table_name = 'item' AND table_schema = 'it_feed'";

	$query = $db->sql_query($sql);

	return $db->sql_fetchrow($query);

}

//

function getTitle($cid) {

	global $db;

	$sql = "SELECT title FROM channel WHERE id = '$cid'";

	$query = $db->sql_query($sql);

	$result = $db->sql_fetchrow($query);

	return htmlspecialchars_decode($result['title'], ENT_QUOTES);
	//return $result['title'];

}

function lastModified() {

	global $db;

	$sql = "SHOW TABLE STATUS WHERE name = 'item'";

	$query = $db->sql_query($sql);

	$result = $db->sql_fetchrow($query);

	return $result['Update_time']."/".$result['Rows'];

}

function getMsgNum($username, $channelid = '') {

	global $db;

	$sql_all = "SELECT COUNT(item.id) AS num
			FROM item INNER JOIN userchannel ON item.channelid = userchannel.channelid 
			WHERE userchannel.username = '" . $username . "' AND item.isPublish = '1'";

	$sql_all .= (empty($channelid) ? "" : " AND item.channelid = '" . $channelid . "'");
		
	$sql_unread = $sql_all . " AND item.isRead = '0'";

	$query_all = $db->sql_query($sql_all);
	$num_all = $db->sql_fetchrow($query_all);
	
	$query_unread = $db->sql_query($sql_unread);
	$num_unread = $db->sql_fetchrow($query_unread);
	
	return array('unread' => $num_unread['num'], 'all' => $num_all['num']);
}

function setAlreadyRead($channelid = '') {

	if(empty($channelid)) {
		return false;
	}

	global $db;

	$sql = "LOCK TABLES item WRITE";
	$db->sql_query($sql);

	$sql = "UPDATE item SET isRead = '1' WHERE isRead = '0' AND
			isPublish = '1' AND channelid = '" . $channelid . "'";
	$flag = $db->sql_query($sql);

	$sql = "UNLOCK TABLES";
	$db->sql_query($sql);

	return $flag;

}

/**
 * Added by Warot, Oct 18th 2008 9:32 pm
 * This function will add new external channel into the database.
 * The table which is inserted data into is 'externalfeed' and 'channel'.
 * Return true if every sql commands are successful, false if one of them got error.
 */

function addExternalChannel($username = '', $title = '', $link) {

	global $db;

	$flag = true;

	$channel = parsing_channel($link);
	$items = parsing_items($link);

	//print_r($channel); return;

	if(empty($channel) && empty($items)) {

		return "Please enter valid web feed url.";

	} else if(empty($channel) || empty($items)) {

		return "This web feed is not supported by MagpieRSS parser.";

	}

	$sql = "LOCK TABLES externalfeed WRITE, channel WRITE, item WRITE";
	$db->sql_query($sql);

	// insert new external feed into externalfeed table

	$sql = "INSERT INTO externalfeed (username, link, lastupdate)
			VALUES ('" . $username . "', '" . $link . "', NOW())";
	$flag &= $db->sql_query($sql);

	if(!$flag) {

		$sql = "UNLOCK TABLES";
		$db->sql_query($sql);

		return "External Channel has already existed.";

	}

	// Add new external channel data into 'channel' table in database

	$flag &= insert_channel_info($channel, $title);

	$channel_id = get_channelid($link);

	$flag &= insert_items($items, $channel_id);

	$sql = "UNLOCK TABLES";
	$db->sql_query($sql);

	return $flag ? "Added" : "Fail";

}

function removeExternalChannel($username, $channel_id) {

	global $db;

	$sql = "LOCK TABLES externalfeed WRITE, channel WRITE, item WRITE";
	$db->sql_query($sql);

	$sql = "SELECT docs FROM channel WHERE id = '" . $channel_id . "'";
	$query = $db->sql_query($sql);
	$row = $db->sql_fetchrow($query);

	$sql = "DELETE FROM externalfeed WHERE username = '" . $username . "' AND link = '" . $row['docs'] . "'";
	$db->sql_query($sql);

	$sql = "DELETE FROM channel WHERE id = '" . $channel_id . "'";
	$db->sql_query($sql);

	$sql = "DELETE FROM item WHERE channelid = '" . $channel_id . "'";
	$db->sql_query($sql);

	$sql = "UNLOCK TABLES";
	$db->sql_query($sql);
}

function wrap($utfStr,$length){
	$vVowels = array("","","","","","","","","","","","","","");
	//$vVowels = "";
	$hLength = 0;
	$tisStr = iconv('utf-8','tis-620',$utfStr);
	$wStr = "";
	for($i=0;$i<strlen($tisStr) && $hLength<=$length;$i++){
		$charAt = substr($tisStr,$i,1);
		$wStr .= $charAt;
		if(!in_array($charAt,$vVowels)){
			$hLength++;
		}
	}
	if($hLength == $length+1)
	$wStr = substr($wStr,0,-1)."...";

	return iconv('tis-620','utf-8',$wStr);
}

function set_item_read($item_id)
{
	global $db;
	
	$sql = "UPDATE item SET isRead = '1' WHERE id = '" . $item_id . "'";
	return $db->sql_query($sql);
}

function get_rss()
{
	global $db;

	$sql = "SELECT id, title FROM channel WHERE docs NOT IN (SELECT link FROM externalfeed GROUP BY link) AND rank = '1'";
	$query = $db->sql_query($sql);

	while($row = $db->sql_fetchrow($query)) {
		$channel[] = $row;
	}

	$db->sql_close();

	return $channel;
}

/*
 * End function
 */
/* not used anymore
function exportItems($items, $display = 0) {

	$html = "";
	
	$format = "
		<div class=\"item\">
		  <div class=\"item-head\">
			<div class=\"item-title\" style=\"color:#000000\"><a class=\"title\">%s</a> (%s)</div>
			<div class=\"item-time\">%s</div>
			<div class=\"item-author\" style=\"display:none\">%s</div>
		  </div>
		  <div class=\"item-content\" style=\"display:none\">%s</div>
		  <div class=\"item-tail\" style=\"display:none\">%s</div>
		</div>
	";

	$display = ($display == 0)? "none" : "block";

	if(!empty($items)) {
	
		//$json = json_encode($items);
		
		foreach($items as $item) {
		
			$html .= sprintf($format, $item[18], $item[4], $item["pubdate"], $item["author"], $item["description"], "");
		
		}

	}

	//echo $json;
	echo $html;
}*/
?>